from datetime import datetime, date, timedelta
from typing import List, Optional, Dict, Any
from sqlalchemy import select, update, delete, desc, and_, or_, func
from sqlalchemy.ext.asyncio import AsyncSession
from module_speech.entity.do.speech_client_usage_do import SpeechClientUsageDO
from module_speech.entity.vo.speech_client_vo import ClientUsageQueryModel


class ClientUsageDao:
    """
    客户端使用统计数据访问层
    """

    @classmethod
    async def record_usage(cls, db: AsyncSession, usage_record: SpeechClientUsageDO) -> SpeechClientUsageDO:
        """
        记录使用统计

        :param db: 数据库会话
        :param usage_record: 使用统计记录
        :return: 新增的使用统计记录
        """
        db.add(usage_record)
        await db.flush()
        await db.refresh(usage_record)
        return usage_record

    @classmethod
    async def get_usage_by_date(cls, db: AsyncSession, client_user_id: int, usage_date: date) -> Optional[SpeechClientUsageDO]:
        """
        根据日期获取使用统计

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param usage_date: 统计日期
        :return: 使用统计记录或None
        """
        query = select(SpeechClientUsageDO).where(
            and_(
                SpeechClientUsageDO.client_user_id == client_user_id,
                SpeechClientUsageDO.usage_date == usage_date
            )
        )
        result = await db.execute(query)
        return result.scalars().first()

    @classmethod
    async def get_usage_by_date_range(cls, db: AsyncSession, client_user_id: int, 
                                      start_date: date, end_date: date) -> List[SpeechClientUsageDO]:
        """
        根据日期范围获取使用统计列表

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param start_date: 开始日期
        :param end_date: 结束日期
        :return: 使用统计记录列表
        """
        query = select(SpeechClientUsageDO).where(
            and_(
                SpeechClientUsageDO.client_user_id == client_user_id,
                SpeechClientUsageDO.usage_date >= start_date,
                SpeechClientUsageDO.usage_date <= end_date
            )
        ).order_by(desc(SpeechClientUsageDO.usage_date))
        
        result = await db.execute(query)
        return result.scalars().all()

    @classmethod
    async def get_usage_list(cls, db: AsyncSession, query_object: ClientUsageQueryModel) -> List[SpeechClientUsageDO]:
        """
        获取使用统计列表

        :param db: 数据库会话
        :param query_object: 查询参数对象
        :return: 使用统计列表
        """
        query = select(SpeechClientUsageDO)
        
        # 添加查询条件
        if query_object.client_user_id:
            query = query.where(SpeechClientUsageDO.client_user_id == query_object.client_user_id)
        if query_object.usage_date_start:
            query = query.where(SpeechClientUsageDO.usage_date >= query_object.usage_date_start)
        if query_object.usage_date_end:
            query = query.where(SpeechClientUsageDO.usage_date <= query_object.usage_date_end)
        
        # 按使用日期倒序排列
        query = query.order_by(desc(SpeechClientUsageDO.usage_date))
        
        # 分页
        offset = (query_object.page_num - 1) * query_object.page_size
        query = query.offset(offset).limit(query_object.page_size)
        
        result = await db.execute(query)
        return result.scalars().all()

    @classmethod
    async def count_usage_records(cls, db: AsyncSession, query_object: ClientUsageQueryModel) -> int:
        """
        统计使用记录总数

        :param db: 数据库会话
        :param query_object: 查询参数对象
        :return: 记录总数
        """
        query = select(func.count(SpeechClientUsageDO.usage_id))
        
        # 添加查询条件
        if query_object.client_user_id:
            query = query.where(SpeechClientUsageDO.client_user_id == query_object.client_user_id)
        if query_object.usage_date_start:
            query = query.where(SpeechClientUsageDO.usage_date >= query_object.usage_date_start)
        if query_object.usage_date_end:
            query = query.where(SpeechClientUsageDO.usage_date <= query_object.usage_date_end)
        
        result = await db.execute(query)
        return result.scalar()

    @classmethod
    async def update_daily_stats(cls, db: AsyncSession, client_user_id: int, usage_date: date,
                                 requests_count: int = 0, duration: float = 0.0, 
                                 characters_count: int = 0, model_name: str = None,
                                 is_success: bool = True, response_time: float = 0.0) -> bool:
        """
        更新日统计数据

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param usage_date: 统计日期
        :param requests_count: 请求数量
        :param duration: 音频时长
        :param characters_count: 字符数量
        :param model_name: 模型名称
        :param is_success: 是否成功
        :param response_time: 响应时间
        :return: 更新是否成功
        """
        # 首先尝试获取现有记录
        existing_record = await cls.get_usage_by_date(db, client_user_id, usage_date)
        
        if existing_record:
            # 更新现有记录
            existing_record.total_requests += requests_count
            existing_record.total_duration += duration
            existing_record.total_characters += characters_count
            existing_record.api_calls += 1
            
            if not is_success:
                existing_record.error_count += 1
            
            # 更新成功率
            total_calls = existing_record.api_calls
            success_calls = total_calls - existing_record.error_count
            existing_record.success_rate = (success_calls / total_calls * 100) if total_calls > 0 else 0
            
            # 更新平均响应时间
            if response_time > 0:
                total_response_time = existing_record.avg_response_time * (total_calls - 1) + response_time
                existing_record.avg_response_time = total_response_time / total_calls
            
            # 更新模型使用统计
            if model_name:
                model_usage = existing_record.model_usage or {}
                model_usage[model_name] = model_usage.get(model_name, 0) + 1
                existing_record.model_usage = model_usage
            
            existing_record.update_time = datetime.now()
            await db.flush()
            return True
        else:
            # 创建新记录
            model_usage = {model_name: 1} if model_name else {}
            success_rate = 100.0 if is_success else 0.0
            error_count = 0 if is_success else 1
            
            new_record = SpeechClientUsageDO(
                client_user_id=client_user_id,
                usage_date=usage_date,
                total_requests=requests_count,
                total_duration=duration,
                total_characters=characters_count,
                model_usage=model_usage,
                api_calls=1,
                success_rate=success_rate,
                avg_response_time=response_time,
                error_count=error_count,
                peak_concurrent=1,
                create_time=datetime.now(),
                update_time=datetime.now()
            )
            
            db.add(new_record)
            await db.flush()
            return True

    @classmethod
    async def get_usage_summary(cls, db: AsyncSession, client_user_id: int,
                                start_date: date = None, end_date: date = None) -> Dict[str, Any]:
        """
        获取使用摘要统计

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param start_date: 开始日期
        :param end_date: 结束日期
        :return: 使用摘要
        """
        query = select(SpeechClientUsageDO).where(SpeechClientUsageDO.client_user_id == client_user_id)
        
        if start_date:
            query = query.where(SpeechClientUsageDO.usage_date >= start_date)
        if end_date:
            query = query.where(SpeechClientUsageDO.usage_date <= end_date)
        
        result = await db.execute(query)
        records = result.scalars().all()
        
        if not records:
            return {
                'total_requests': 0,
                'total_duration': 0.0,
                'total_characters': 0,
                'total_api_calls': 0,
                'avg_success_rate': 0.0,
                'avg_response_time': 0.0,
                'total_errors': 0,
                'model_usage': {},
                'daily_stats': []
            }
        
        # 汇总统计
        total_requests = sum(r.total_requests for r in records)
        total_duration = sum(r.total_duration for r in records)
        total_characters = sum(r.total_characters for r in records)
        total_api_calls = sum(r.api_calls for r in records)
        total_errors = sum(r.error_count for r in records)
        
        # 计算加权平均成功率和响应时间
        weighted_success_rate = sum(r.success_rate * r.api_calls for r in records if r.api_calls > 0)
        avg_success_rate = weighted_success_rate / total_api_calls if total_api_calls > 0 else 0
        
        weighted_response_time = sum(r.avg_response_time * r.api_calls for r in records if r.api_calls > 0)
        avg_response_time = weighted_response_time / total_api_calls if total_api_calls > 0 else 0
        
        # 汇总模型使用统计
        model_usage = {}
        for record in records:
            if record.model_usage:
                for model, count in record.model_usage.items():
                    model_usage[model] = model_usage.get(model, 0) + count
        
        # 每日统计详情
        daily_stats = []
        for record in records:
            daily_stats.append({
                'date': record.usage_date.isoformat(),
                'requests': record.total_requests,
                'duration': float(record.total_duration),
                'characters': record.total_characters,
                'api_calls': record.api_calls,
                'success_rate': float(record.success_rate),
                'avg_response_time': float(record.avg_response_time),
                'errors': record.error_count,
                'model_usage': record.model_usage or {}
            })
        
        return {
            'total_requests': total_requests,
            'total_duration': float(total_duration),
            'total_characters': total_characters,
            'total_api_calls': total_api_calls,
            'avg_success_rate': float(avg_success_rate),
            'avg_response_time': float(avg_response_time),
            'total_errors': total_errors,
            'model_usage': model_usage,
            'daily_stats': sorted(daily_stats, key=lambda x: x['date'], reverse=True)
        }

    @classmethod
    async def get_monthly_usage(cls, db: AsyncSession, client_user_id: int, 
                                year: int = None, month: int = None) -> Dict[str, Any]:
        """
        获取月度使用统计

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param year: 年份
        :param month: 月份
        :return: 月度使用统计
        """
        if not year or not month:
            today = date.today()
            year = today.year
            month = today.month
        
        # 计算月份的开始和结束日期
        start_date = date(year, month, 1)
        if month == 12:
            end_date = date(year + 1, 1, 1) - timedelta(days=1)
        else:
            end_date = date(year, month + 1, 1) - timedelta(days=1)
        
        return await cls.get_usage_summary(db, client_user_id, start_date, end_date)

    @classmethod
    async def get_top_users_by_usage(cls, db: AsyncSession, start_date: date = None, 
                                     end_date: date = None, limit: int = 10) -> List[Dict[str, Any]]:
        """
        获取使用量最高的用户排行

        :param db: 数据库会话
        :param start_date: 开始日期
        :param end_date: 结束日期
        :param limit: 返回数量限制
        :return: 用户使用排行列表
        """
        query = select(
            SpeechClientUsageDO.client_user_id,
            func.sum(SpeechClientUsageDO.total_requests).label('total_requests'),
            func.sum(SpeechClientUsageDO.total_duration).label('total_duration'),
            func.sum(SpeechClientUsageDO.total_characters).label('total_characters'),
            func.sum(SpeechClientUsageDO.api_calls).label('total_api_calls')
        )
        
        if start_date:
            query = query.where(SpeechClientUsageDO.usage_date >= start_date)
        if end_date:
            query = query.where(SpeechClientUsageDO.usage_date <= end_date)
        
        query = query.group_by(SpeechClientUsageDO.client_user_id)\
                     .order_by(desc('total_requests'))\
                     .limit(limit)
        
        result = await db.execute(query)
        rows = result.all()
        
        return [
            {
                'client_user_id': row.client_user_id,
                'total_requests': row.total_requests,
                'total_duration': float(row.total_duration or 0),
                'total_characters': row.total_characters,
                'total_api_calls': row.total_api_calls
            }
            for row in rows
        ]

    @classmethod
    async def get_system_usage_stats(cls, db: AsyncSession, start_date: date = None, 
                                     end_date: date = None) -> Dict[str, Any]:
        """
        获取系统总体使用统计

        :param db: 数据库会话
        :param start_date: 开始日期
        :param end_date: 结束日期
        :return: 系统使用统计
        """
        query = select(
            func.sum(SpeechClientUsageDO.total_requests).label('total_requests'),
            func.sum(SpeechClientUsageDO.total_duration).label('total_duration'),
            func.sum(SpeechClientUsageDO.total_characters).label('total_characters'),
            func.sum(SpeechClientUsageDO.api_calls).label('total_api_calls'),
            func.sum(SpeechClientUsageDO.error_count).label('total_errors'),
            func.count(func.distinct(SpeechClientUsageDO.client_user_id)).label('active_users')
        )
        
        if start_date:
            query = query.where(SpeechClientUsageDO.usage_date >= start_date)
        if end_date:
            query = query.where(SpeechClientUsageDO.usage_date <= end_date)
        
        result = await db.execute(query)
        row = result.first()
        
        if not row or not row.total_api_calls:
            return {
                'total_requests': 0,
                'total_duration': 0.0,
                'total_characters': 0,
                'total_api_calls': 0,
                'total_errors': 0,
                'active_users': 0,
                'success_rate': 0.0
            }
        
        success_rate = ((row.total_api_calls - row.total_errors) / row.total_api_calls * 100) if row.total_api_calls > 0 else 0
        
        return {
            'total_requests': row.total_requests or 0,
            'total_duration': float(row.total_duration or 0),
            'total_characters': row.total_characters or 0,
            'total_api_calls': row.total_api_calls or 0,
            'total_errors': row.total_errors or 0,
            'active_users': row.active_users or 0,
            'success_rate': float(success_rate)
        }

    @classmethod
    async def cleanup_old_usage_records(cls, db: AsyncSession, days_to_keep: int = 365) -> int:
        """
        清理旧的使用记录

        :param db: 数据库会话
        :param days_to_keep: 保留天数
        :return: 删除的记录数
        """
        cutoff_date = date.today() - timedelta(days=days_to_keep)
        
        query = delete(SpeechClientUsageDO).where(SpeechClientUsageDO.usage_date < cutoff_date)
        result = await db.execute(query)
        
        return result.rowcount 